package com.Spring.SpringSpms.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import com.Spring.SpringSpms.model.Employee;


import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

/**
 * An implementation of the ContactDAO interface.
 * @author kalyani
 *
 */
public class EmployeeDAOImpl implements EmployeeDAO{

private JdbcTemplate jdbcTemplate;
	
	public EmployeeDAOImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public List<Employee> list() {
		String sql = "SELECT * FROM Employee";
		List<Employee> listEmployee = jdbcTemplate.query(sql, new RowMapper<Employee>() {

			@Override
			public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
				Employee aempl = new Employee();
	
				aempl.setEmp_id(rs.getInt("emp_id"));
				aempl.setFirst_name(rs.getString("first_name"));
				aempl.setLast_name(rs.getString("last_name"));
				aempl.setEmail_id(rs.getString("email_id"));
				aempl.setDesignation_id(rs.getInt("designation_id"));
				aempl.setPassword(rs.getString("password"));
				return aempl;
			}
			
		});
		
		return listEmployee;
	}
	
/*	@Override
	public Employee get(int empid) {
		String sql = "SELECT * FROM Employee WHERE emp_id=" + empid;
		return jdbcTemplate.query(sql, new ResultSetExtractor<Employee>() {

			@Override
			public Employee extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Employee employee = new Employee();
					employee.setId(rs.getInt("emp_id"));
					employee.setFname(rs.getString("first_name"));
					employee.setLname(rs.getString("last_name"));
					employee.setEmail(rs.getString("email_id"));
					
					return employee;
				}
				
				return null;
			}
			
		});
	}*/
	
	@Override
	public void saveOrUpdate(Employee employee) {
		if (employee.getEmp_id() > 0) {
			// update
			String sql = "UPDATE employee SET first_name=?, last_name=?, designation_id=?, "
						+ "email_id=?, password=? WHERE emp_id=?";
			jdbcTemplate.update(sql, employee.getFirst_name(), employee.getLast_name(), 
								employee.getDesignation_id(), employee.getEmail_id(),
								employee.getPassword(), employee.getEmp_id());
			
		} else {
			// insert
			String sql = "INSERT INTO employee (first_name, last_name, designation_id, email_id, password)"
						+ " VALUES (?, ?, ?, ?, ?)";
			jdbcTemplate.update(sql, employee.getFirst_name(), employee.getLast_name(), 
								employee.getDesignation_id(), employee.getEmail_id(), 
								employee.getPassword());
			
			//String sql1 = "insert into employee_role(emp_id,role_id) values(?,?)";
			//jdbcTemplate.update(sql1,employee.getId(),employee.getRole_id());
		}
	}

	@Override
	public void delete(int emplpoyeeId) {
		String sql = "DELETE FROM employee WHERE emp_id=?";
		jdbcTemplate.update(sql, emplpoyeeId);
	}

	
	@Override
	public int loginTest(Employee employee,Integer roleId)
	{
		String name1= employee.getFirst_name();
		System.out.print(name1);
		String[] name=name1.split("\\.");
		if(name.length>1)
		{
		String sql="SELECT count(*) FROM Employee WHERE first_name='"+name[0]+"'and last_name='"+name[1]+"' and password='"+employee.getPassword()+"'"; 
		
		int count=jdbcTemplate.queryForInt(sql);
		if(count>0)
		{
			String sql2="SELECT emp_id FROM Employee WHERE first_name='"+name[0]+"'and last_name='"+name[1]+"' and password='"+employee.getPassword()+"'"; 
			
			int count2=jdbcTemplate.queryForInt(sql2);
		String sql1="SELECT count(*) from employee_role where emp_id='"+count2+"'and role_id='"+roleId+"'";
		int cnt1=jdbcTemplate.queryForInt(sql1);
				if(cnt1>0)
		return count2;
		else
			return 0;
		}
		else
			return 0;
		}
		else 
			return 0;
		
	}

	@Override
	public Employee get(int empId) {
		// TODO Auto-generated method stub
		return null;
	}
	public String getUserName(int empid) {
		String username = null;
		String sql="select first_name from Employee where emp_id="+empid;
		String firstname=jdbcTemplate.queryForObject(sql, String.class);
		String sql1="select last_name from Employee where emp_id="+empid;
		String lastname=jdbcTemplate.queryForObject(sql1, String.class);
		username=firstname+"."+lastname;
		return username;
	}



	@Override
	public List<Employee> listProjectEmployees(int projid) {
		String sql = "SELECT * FROM Employee,employee_project where Employee.emp_id=employee_project.emp_id and employee_project.project_id='"+projid+"'";
		List<Employee> listEmployee = jdbcTemplate.query(sql, new RowMapper<Employee>() {

			@Override
			public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
				Employee aempl = new Employee();
	
				aempl.setEmp_id(rs.getInt("emp_id"));
				aempl.setFirst_name(rs.getString("first_name"));
				aempl.setLast_name(rs.getString("last_name"));
				aempl.setEmail_id(rs.getString("email_id"));
				aempl.setDesignation_id(rs.getInt("designation_id"));
				return aempl;
			}
			
		});
		
		return listEmployee;
	}


}
